package com.augurit.common.sysmeta.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;

import com.augurit.common.dao.SysMetaColumnDao;
import com.augurit.common.dao.SysMetaTableDao;
import com.augurit.common.entity.SysMetaColumn;
import com.augurit.common.entity.SysMetaTable;
import com.augurit.common.syscode.service.ISysCodeService;
import com.augurit.common.syscode.web.form.SysCodeForm;
import com.augurit.common.sysmeta.convert.SysMetaColumnConvertor;
import com.augurit.common.sysmeta.service.ISysMetaColumnService;
import com.augurit.common.sysmeta.web.form.SysMetaColumnForm;
import com.augurit.common.util.HqlUtils;
import com.augurit.common.util.PageUtils;

/**
 * 字段结构元数据定义实现类
 *
 */
@Service
@Transactional
public class SysMetaColumnServiceImpl implements ISysMetaColumnService{

	@Autowired
	private SysMetaColumnDao sysMetaColumnDao;
	
	@Autowired 
	private SysMetaTableDao sysMetaTableDao;
	
	@Autowired
	private ISysCodeService sysCodeService;
	
	
	public void delete(Long... ids) {
		if(ids != null && ids.length>0)
			sysMetaColumnDao.delete(ids);
	}

	/**
	 * 根据id获取字段form对象,并加载tableName属性
	 * @param id 
	 * @return 
	 */
	public SysMetaColumnForm get(Long id) {
		SysMetaColumnForm form = null;
		if(id != null){
			form  = sysMetaColumnDao.getForm(id);
			
			//加载tableName属性
			SysMetaTable sysMetaTable = this.sysMetaTableDao.get(form.getTableId());
			if(sysMetaTable != null)
				form.setTableName(sysMetaTable.getTableName());
		}
		return form;
	}

	public void save(SysMetaColumnForm... forms) {
		if(forms != null && forms.length>0){
			for(SysMetaColumnForm form : forms)
				sysMetaColumnDao.save(form);
		}
		
	}

	/**
	 * 根据tableId获取form对象列表
	 * @param id
	 * @return
	 */
	public Page<SysMetaColumnForm> getColumnsByTableId(Long tableId,Page<SysMetaColumnForm> page, Map<String, List<SysCodeForm>> sysCodeBuffer){
		if(tableId != null && page != null){
			// 建立分页对象
			Page<SysMetaColumn> pg = PageUtils.newPageInstance(page);
			
			String hql = "from SysMetaColumn ps where ps.tableId="+tableId;
			 hql = hql + HqlUtils.buildOrderBy(pg, "ps");
			//查询结果
			pg = sysMetaColumnDao.findPage(pg,hql);
			List<SysMetaColumnForm> forms = SysMetaColumnConvertor.convertEntityListToFormList(
					pg.getResult(),
					sysCodeService.getItems(this.SYS_CODE_NULLABLE, sysCodeBuffer), 
					sysCodeService.getItems(this.SYS_CODE_ISPRIMARYKEY, sysCodeBuffer));
			this.loadTableName(forms);
			PageUtils.copy(pg, forms, page);
		}
		return page;
	}
	
	/**
	 * 根据tableId获取form对象列表
	 * @param id
	 * @return
	 */
	public List<SysMetaColumnForm> getColumnsByTableId(Long tableId){
		if(tableId != null){
			return this.sysMetaColumnDao.getColumnsByTableId(tableId);
		}
		return null;
	}
	/**
	 * 查询方法
	 * @param form
	 * @param page
	 * @return
	 */
	public Page<SysMetaColumnForm> search(SysMetaColumnForm form,Page<SysMetaColumnForm> page, Map<String, List<SysCodeForm>> sysCodeBuffer){
		// 建立分页对象
		Page<SysMetaColumn> pg = PageUtils.newPageInstance(page);
		
		StringBuffer sb =  new StringBuffer("from SysMetaColumn ps where 1=1");
		
		// 查询条件
		if(form.getColumnName() != null && form.getColumnName().trim().length()>0)
			sb.append(" and ps.columnName like '%" + form.getColumnName().trim() + "%'");
		if(form.getColumnComments() != null && form.getColumnComments().trim().length()>0)
			sb.append(" and ps.columnComments like '%" + form.getColumnComments().trim() + "%'");
		if(form.getColumnType() != null && form.getColumnType().trim().length()>0)
			sb.append(" and ps.columnType like '%" + form.getColumnType().trim() + "%'");
		if(form.getIsPrimaryKey() != null && form.getIsPrimaryKey().trim().length()>0)
			sb.append(" and ps.isPrimaryKey like '%" + form.getIsPrimaryKey().trim() + "%'");
		if(form.getNullable() != null && form.getNullable().trim().length()>0)
			sb.append(" and ps.nullable like '%" + form.getNullable().trim() + "%'");
		
		sb.append(HqlUtils.buildOrderBy(pg, "ps"));
		
		String hql = sb.toString();
		
		//查询结果
		pg = sysMetaColumnDao.findPage(pg,hql);
		List<SysMetaColumnForm> forms = SysMetaColumnConvertor.convertEntityListToFormList(pg.getResult(),
				sysCodeService.getItems(this.SYS_CODE_NULLABLE, sysCodeBuffer), 
				sysCodeService.getItems(this.SYS_CODE_ISPRIMARYKEY, sysCodeBuffer));
		loadTableName(forms);
		
		PageUtils.copy(pg, forms, page);
		return page;
	}
	
	public Page<SysMetaColumnForm> getColumnsGroupByTableIds(Page<SysMetaColumnForm>page,Long tableIds[],String condition){
		
		if(page != null && tableIds != null && tableIds.length>0 && condition != null && condition.trim().length()>0){
			// 建立分页对象
			Page<SysMetaColumn> pg = PageUtils.newPageInstance(page);
			
			StringBuffer sb =  new StringBuffer("from SysMetaColumn ps where (1=2");
			
			for(Long tableId :tableIds){
				sb.append( " or ps.tableId ="+ tableId);
			}
				sb.append(" or 1=3)");
				sb.append(condition);
			String hql = sb.toString()+" order by ps.tableId";
			
			//查询结果
			pg = sysMetaColumnDao.findPage(pg,hql);
			List<SysMetaColumnForm> forms = SysMetaColumnConvertor.convertEntityListToFormList(pg.getResult());
			loadTableName(forms);
			PageUtils.copy(pg, forms, page);
		}
		return page;
	}
	
	/**
	 * 根据表实体名获取字段page
	 * @param page
	 * @param entityNames
	 * @return
	 */
	public Page<SysMetaColumnForm> getColumnsGroupByEntityNames(Page<SysMetaColumnForm>page,String entityNames[],String condition){
		
		if(page != null && entityNames != null && entityNames.length>0){
			//将entityNames变为tableIds
			Long tableIds[] = sysMetaTableDao.getTableIdsByEntityNames(entityNames);
			return getColumnsGroupByTableIds(page,tableIds,condition);
		}
		return page;
	}
	
	/**
	 * 根据表表名获取字段page
	 * @param page
	 * @param tableNames
	 * @return
	 */
	public Page<SysMetaColumnForm> getColumnsGroupByTableNames(Page<SysMetaColumnForm>page,String tableNames[],String condition){
		
		if(page != null && tableNames != null && tableNames.length>0){
			//将entityNames变为tableIds
			Long tableIds[] = sysMetaTableDao.getTableIdsByTableNames(tableNames);
			return getColumnsGroupByTableIds(page,tableIds,condition);
		}
		return page;
	}
	
	public List<SysMetaColumnForm> getColumnsGroupByTableNames(SysMetaColumnForm form, String tableNames[]){
		List<SysMetaColumnForm> result = null;
		if(tableNames != null && tableNames.length > 0){
			StringBuilder hql = new StringBuilder("select column ");
			hql.append(" from SysMetaColumn column, SysMetaTable table where column.tableId = table.id");
			
			hql.append(" and ");
			hql.append(HqlUtils.buildOrCondition("table", "tableName", tableNames));
			
			List<Object> values = new ArrayList<Object>();
			
			if(form.getColumnName() != null && form.getColumnName().trim().length() > 0){
				hql.append(" and column.columnName like ?");
				values.add("%" + form.getColumnName() + "%");
			}
			
			if(form.getColumnComments() != null && form.getColumnComments().trim().length() > 0){
				hql.append(" and column.columnComments like ?");
				values.add("%" + form.getColumnComments() + "%");
			}
			hql.append(" and column.isPrimaryKey='N'");
			
			List<SysMetaColumn> objs = sysMetaColumnDao.find(hql.toString(), values) ;
			
			if(objs != null && objs.size() > 0){
				result = new ArrayList<SysMetaColumnForm>();
				
				for(SysMetaColumn entity:objs){
					SysMetaColumnForm elem = SysMetaColumnConvertor.convertToForm(entity);
					
					SysMetaTable table = sysMetaTableDao.get(entity.getTableId());
					elem.setTableName(table.getTableName());
					elem.setEntityName(table.getEntityName());
					
					result.add(elem);
				}
			}
		}
		return result;
	}
	
	
	public List<SysMetaColumnForm> getColumnsByPropertyNames(String busEntityName,String queryProperties){
		List<SysMetaColumnForm> result = null;
		if(busEntityName != null && busEntityName.trim().length()>0 
				&& queryProperties != null && queryProperties.trim().length()>0){
			StringBuffer sb = new StringBuffer("from SysMetaColumn ps where 1=2");
			String strs[] = queryProperties.split(",");
			for(String str:strs){
				String names[] = str.split("\\.");
				String entityName = names[0];
				String propertyName = names[1];
				if(entityName.equals("bus"))
					entityName = busEntityName;
				
				//组建hql语句
				sb.append("or (ps.tableId =(select smt.id from SysMetaTable smt where smt.entityName='"+entityName +"') and ps.propertyName='"+propertyName+"')");
				
			}
			List<SysMetaColumn> list = this.sysMetaColumnDao.find(sb.toString());
			
			if(list != null && list.size()>0){
				result = SysMetaColumnConvertor.convertEntityListToFormList(list);
				loadTableName(result);
			}
		}
		return result;
	}
	
	
	/**
	 * 为字段form对象列表全部加载tableName属性
	 * @param forms
	 */
	private void loadTableName(List<SysMetaColumnForm> forms){
		if(forms != null && forms.size()>0){
			for(SysMetaColumnForm form :forms){
				SysMetaTable sysMetaTable = sysMetaTableDao.get(form.getTableId());
				if(sysMetaTable != null){
					form.setTableName(sysMetaTable.getTableName());
					form.setEntityName(sysMetaTable.getEntityName());
				}
			}
		}
	}
	
	
	/**
	 * 
	 * @param tableColumnPairs
	 */
	public List<SysMetaColumnForm> getColumnsByTablesAndColumns(HqlUtils.TableColumnPair[] tableColumnPairs){
		List<SysMetaColumnForm> result = null;
		if(tableColumnPairs != null && tableColumnPairs.length > 0){
			List<Object> values = new ArrayList<Object>();
			
			StringBuilder sql = new StringBuilder("select col.*, tab.entity_name as entityName, tab.table_name as tableName");
			sql.append(" from sys_meta_column col, sys_meta_table tab");
			sql.append(" where col.table_id=tab.id ");
			
			sql.append(" and (");
			for(int i = 0; i < tableColumnPairs.length; i++){
				HqlUtils.TableColumnPair pair = tableColumnPairs[i];
				
				
				sql.append("(col.column_name=? and tab.table_name=?)");
				if( i != tableColumnPairs.length - 1)
					sql.append(" or ");
				values.add(pair.getColumnName());
				values.add(pair.getTableName());
				
				
			}
			sql.append(")");
			
			
			SQLQuery query = sysMetaColumnDao.getSession().createSQLQuery(sql.toString());
			query.addEntity(SysMetaColumn.class).addScalar("entityName").addScalar("tableName");
			for(int i = 0; i < values.size(); i++){
				query.setParameter(i, values.get(i));
			}
			List<Object[]> objs = (List<Object[]>)query.list();
			if(objs != null && objs.size() > 0){
				result = new ArrayList<SysMetaColumnForm>();
				for(int i = 0; i < objs.size(); i++){
					SysMetaColumnForm form = SysMetaColumnConvertor.convertToForm((SysMetaColumn)objs.get(i)[0]);
					form.setEntityName((String)objs.get(i)[1]);
					form.setTableName((String)objs.get(i)[2]);
					
					result.add(form);
				}
			}
		}
		return result;
	}

}
